import datetime
import os
import sys
import calendar

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from typing import List
from src.tasks.enterprise.EntEnterprise import EntEnterprise
from src.utils import CommonUtils, BatchUtils


class SyncAbsBigData:
    def __init__(self, logger, localDb, absDb):
        self.logger = logger
        self.localDb = localDb
        self.absDb = absDb

    # #  证券存量统计
    def __updStockData__(self):
        flag = 0
        count = 0
        # str_time = datetime.datetime.strptime('2024-02-29', "%Y-%m-%d")
        #
        # print(is_end_of_month(str_time))  # 替换today为你想要检查的日期
        # 加载需要同步的数据
        timeData = self.__querySyncTimeLine__()
        if timeData is None:
            timeList = self.__queryAccountDate__(50000)
            for i in timeList:
                if (is_end_of_month(i["Date"])):
                    dataInfo = self.__queryStockDataByDate__(i["Date"],i["Date"])
                    updateDatas = []
                    for r in dataInfo:
                        updateDatas.append(list(r.values()))
                    print("截面日期"+ str(i["Date"])+"数据插入")
                    self.__deleteStockData__(i["Date"])
                    BatchUtils.batchInsert(self.__InsertStockDataData__, updateDatas)
        else:
            timeList = self.__queryAccountDate__(2000)
            for i in timeList:
                if (count < 13):
                    if (is_end_of_month(i["Date"]) or count == 0):
                        dataInfo = self.__queryStockDataByDate__(i["Date"],i["Date"])
                        updateDatas = []
                        for r in dataInfo:
                            updateDatas.append(list(r.values()))
                        print("截面日期"+ str(i["Date"])+"数据插入")
                        self.__deleteStockData__(i["Date"])
                        BatchUtils.batchInsert(self.__InsertStockDataData__, updateDatas)
                        count = count + 1

    def __queryAccountDate__(self, lineNum):
            sql = """
                SELECT * FROM abs_cross_sectional_datas WHERE IsDeleted = 0  GROUP BY DATE order by date desc limit %s 
            """
            return self.absDb.Query(sql, lineNum)

    def __deleteStockData__(self, date):
        sql = """delete from abs_stock_data_v2 where accountDate = DATE(%s) """
        return self.localDb.ExecNoneQuery(sql, date)

    def __queryStockDataByDate__(self,date1, date2):
            sql = """
                SELECT concat(ad.id,"-",s.date)AS id,  ad.id AS rownum, ad.BondId, ad.BondCode,ad.BondType,ad.Name,ad.ShortName,ad.Exchange,ad.IssueAmount,s.date AS accountDate,s.balance,s.MaturityDate,ad.InitialTranchePercentage, ad.RepaymentType,ad.Coupon,ad.CouponType,ad.BaseRateName,ad.Frequency,ad.FirstInterestAccrualDate,ad.IssueBeginDate,ad.IssueEndDate,aa.LegalMaturityDate, ad.EstimatedMaturityDate,ad.Term,ad.InitialPrice,ad.OriginalRating,ad.OriginalRatingAgency,aa.NAME AS ProductName, aa.ShortName AS ProductShortName, aa.STATUS,aa.SettlementDate,aa.CNABSCategory1,aa.CNABSCategory2,aa.CNABSCategory3,aa.Manager,aa.Originator,aa.ActualFinancier,aa.CreditProvider,aa.Servicer,GROUP_CONCAT(ee.tag) as Tag
                FROM abs_bonds ad
                LEFT JOIN abs_cross_sectional_datas s ON s.BondId = ad.bondid AND DATE(s.Date) = DATE(%s)
                LEFT JOIN abs_deals aa ON ad.dealid=aa.dealid
                LEFT JOIN abs_deals_tag ee ON ad.dealid=ee.dealid AND ee.IsDeleted=0
                WHERE ad.IsDeleted=0 AND DATE(s.Date) = DATE(%s)
                GROUP BY ad.id
                ORDER BY FirstInterestAccrualDate DESC, ad.id
            """
            return self.absDb.Query(sql, (date1, date2))

    def __InsertStockDataData__(self, updateDatas):
        sql = """
        INSERT INTO `abs_stock_data_v2`  (`id`, `rownum`, `BondId`, `BondCode`, `BondType`, `Name`, `ShortName`, `Exchange`, `IssueAmount`, `accountDate`, `balance`, `MaturityDate`, `InitialTranchePercentage`, `RepaymentType`, `Coupon`, `CouponType`, `BaseRateName`, `Frequency`, `FirstInterestAccrualDate`, `IssueBeginDate`, `IssueEndDate`, `LegalMaturityDate`, `EstimatedMaturityDate`, `Term`, `InitialPrice`, `OriginalRating`, `OriginalRatingAgency`, `ProductName`, `ProductShortName`, `STATUS`, `SettlementDate`, `CNABSCategory1`, `CNABSCategory2`, `CNABSCategory3`, `Manager`, `Originator`, `ActualFinancier`, `CreditProvider`, `Servicer`, `Tag`) 
         VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    # def __UpdNewData__(self, updateDatas):
    #     sql = """
    #     REPLACE INTO `abs_stock_data_v2` (`id`, `rownum`, `BondId`, `BondCode`, `BondType`, `Name`, `ShortName`, `Exchange`, `IssueAmount`, `accountDate`, `balance`, `MaturityDate`, `InitialTranchePercentage`, `RepaymentType`, `Coupon`, `CouponType`, `BaseRateName`, `Frequency`, `FirstInterestAccrualDate`, `IssueBeginDate`, `IssueEndDate`, `LegalMaturityDate`, `EstimatedMaturityDate`, `Term`, `InitialPrice`, `OriginalRating`, `OriginalRatingAgency`, `ProductName`, `ProductShortName`, `STATUS`, `SettlementDate`, `CNABSCategory1`, `CNABSCategory2`, `CNABSCategory3`, `Manager`, `Originator`, `ActualFinancier`, `CreditProvider`, `Servicer`, `Tag`)
    #      VALUES
    #     (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
    #     """
    #     return self.localDb.ExecMany(sql, updateDatas)

    def __querySyncTimeLine__(self):
        sql = """SELECT DATE_FORMAT(accountDate, '%Y-%m-%d') AS accountDate from abs_stock_data_v2 
                 order by accountDate desc LIMIT 1 """
        return self.localDb.QueryOne(sql)

    def __updFullStockData__(self):
        count = 0
        self.__deleteFullStockData__()

        timeList = self.__queryAccountDate__(50000)
        for i in timeList:
            if (is_end_of_month(i["Date"])):
                dataInfo = self.__queryStockDataByDate__(i["Date"],i["Date"])
                updateDatas = []
                for r in dataInfo:
                    updateDatas.append(list(r.values()))
                print("截面日期"+ str(i["Date"])+"数据插入")
                self.__deleteStockData__(i["Date"])
                BatchUtils.batchInsert(self.__InsertStockDataData__, updateDatas)


    def __deleteFullStockData__(self):
        sql = """delete from abs_stock_data_v2 """
        return self.localDb.ExecNoneQuery(sql)

def is_end_of_month(date_obj):
    """
    判断传入的日期是否为月末
    :param date_obj: datetime对象
    :return: 如果是月末返回True，否则返回False
    """
    # 获取日期所在年份和月份
    year = date_obj.year
    month = date_obj.month

    # 使用calendar模块获取该月的天数
    last_day_of_month = calendar.monthrange(year, month)[1]

    # 判断传入的日期是否为该月的最后一天
    return date_obj.day == last_day_of_month